var dbpools = require("../utils/dbpools");
var pool1 = dbpools.getPool("_tinypng");
var pool2 = dbpools.getPool("_chatgptrelay");
var async = require("async");
var env = require("../utils/env");
// 初始化
exports.init = function (options) {
    var pool = null;
    if (options.pool == 'chatgptrelay') {
        // 更新pool
        pool = pool2;
    } else {
        pool = pool1;
    }
    // 数据表
    var tablename = options.tablename;
    // 主键
    var primarykey = options.primarykey;
    // 排序值
    var sortkey = options.sortkey;


    /**
     * 根据factid查询工厂信息
     * @Author   huangshoalu
     * @DateTime 2019-04-26
     * @param    {Function}  callback 回调函数
     */
    function queryById (callback, id, opts) {
        pool.query("select * from " + (opts && opts.tablename || tablename) + " where " + primarykey + " = ?", [id], function (err, data) {
            callback(err, data);
        });
    }

    /**
     * 新增工厂信息
     * @Author   huangshoalu
     * @DateTime 2019-04-26
     * @param    {Function}  callback 回调函数
     * @param    {Object}    infos    新增信息
     */
    function add (callback, infos, options) {
        // options = {key: xx}
        // 如果infos里面有主键，就直接判断成有了
        if (infos[primarykey]) {
            if (!options) {
                options = {};
            }
            options.key = primarykey;
        }
        // 如果存在的话，就要判断一下，之前是否存在了
        if (options && options.key && infos[options.key]) {
            // 根据key去查询，如果存在就更新，而不是新增
            pool.query("select * from " + (options && options.tablename || tablename) + " where " + options.key + " = ?", [infos[options.key]], function (err, data) {
                if(err || (data && data.length == 0)){
                    doAdd();
                } else {
                    // 要更新，更新就把createtime去掉
                    delete infos.createtime;
                    update(function (err1, data1) {
                        if (!data1) {
                            data1 = {};
                        }
                        data1.insertId = data[0].id || 0;
                        callback(err1, data1);
                    }, infos, data[0][primarykey], {
                        tablename: options && options.tablename || ""
                    });
                }
            });
        } else {
            doAdd();
        }

        function doAdd () {
            pool.query("insert into " + (options && options.tablename || tablename) + " set ?", infos, function(err, data) {
                callback(err, data);
            });
        }
    }

    /**
     * 更新工厂信息
     * @Author   huangshoalu
     * @DateTime 2019-04-26
     * @param    {Function}  callback 回调函数
     * @param    {Object}    infos    内容
     * @param    {String}    factid   工厂ID
     */
    function update (callback, infos, apply_id, opts) {
        /* apply_id
            {
                status: [{    // 在申请中的
                    type: ">=",
                    value: "5"
                }, {
                    type: "!=",
                    value: "6"
                }],
                factory_id: {    // 有对应factid的
                    type: "=",
                    value: obj.factid
                }
            }
         */

        var sql    = "update " + (opts && opts.tablename || tablename) + " set ";
        var _param = [];
        //将数据加入sql中去
        for (var i in infos) {
            sql += i + " = ? , ";
            _param.push(infos[i]);
        }
        // 去掉多余的逗号
        sql = sql.replace(/, $/, "");

        //设置where条件
        if (typeof apply_id != "object") {
            sql += " where " + primarykey + " = ?";
            _param.push(apply_id);
        } else {
            var where = "";
            // 按条件搜索
            for (var i in apply_id) {
                if (apply_id[i] instanceof Array) {
                    apply_id[i].forEach(function (ceil) {
                        where += " " + i + " " + ceil.type + " ? and ";
                        _param.push(ceil.type == "like" ? ("%" + ceil.value + "%") : ceil.value);
                    });
                } else {
                    where += " " + i + " " + apply_id[i].type + " ? and ";
                    _param.push(apply_id[i].type == "like" ? ("%" + apply_id[i].value + "%") : apply_id[i].value);
                }
            }
            where && (where = ' where' + where.replace(/and *$/, "").replace(/or *$/, ""));
            sql += where;
        }

        // 做一下判断，如果没有where，直接返回异常
        if (sql.indexOf("where") == -1) {
            callback("no where");
            return false;
        }
        var _t = pool.query(sql, _param, function (err, result) {
            callback(err, result);
        });
    }

    // +1
    function addone (callback, colum, id, options) {
        // UPDATE comics set readcount = readcount + 1 WHERE id = 12;
        var sql    = "update " + (options && options.tablename || tablename) + " set " + colum + " = " + colum + " + 1 where " + primarykey + " = ?";
        var _param = [id];
        var _t = pool.query(sql, _param, function (err, result) {
            callback(err, result);
        });
    }

    /**
     * 根据条件查询
     * @Author   huangshoalu
     * @DateTime 2019-04-26
     * @param    {Function}  callback  回调函数
     * @param    {Number}    pagenum   页码
     * @param    {Number}    pagesize  每页长度
     * @param    {Object}    searchobj 搜索条件
     */
    function queryList (callback, searchobj, option) {
        // searchobj = {
        //      begin_time: {
        //          type: "<=",//= like
        //          value: "xxx"
        //      }
        // }
        if (!option) {
            option = {};
        }
        if ((option.pagenum && option.pagenum == "0") || !option.pagenum) {
            option.pagenum = 1;
        }
        option.pagesize = option.pagesize || 10;
        var pagingParam = [(option.pagenum - 1) * option.pagesize, option.pagesize * 1.0];
        var paramArr = [];
        var where = "";
        // 按条件搜索
        if (searchobj instanceof Array) {
            // 有数组就要特殊处理
            searchobj.forEach(function (ceilsearchobj) {
                where += " (";
                doWhere(ceilsearchobj);
                where += ") or ";
            });
            where && (where = where.replace(/and *$/, "").replace(/or *$/, ""));
        } else {
            doWhere(searchobj);
        }

        // where走一下
        function doWhere (mysearchobj, isor) {
            for (var i in mysearchobj) {
                if (i == '_childs' && mysearchobj[i].length) {
                    // or类型
                    where += " (";
                    mysearchobj[i].forEach(function (ceil) {
                        // if (ceil.type == "in") {
                        //     where += " " + i + " in (?) or ";
                        // } else {
                        //     where += " " + i + " " + ceil.type + " ? or ";
                        // }
                        // paramArr.push(ceil.type == "like" ? (ceil.value.indexOf("%") != -1 ? ceil.value : ("%" + ceil.value + "%")) : ceil.value);

                        // for (var j in ceil) {

                        // }
                        doWhere(ceil, true);
                    });
                    where = where.replace(/and *$/, "").replace(/or *$/, "");
                    if (where[where.length - 1] == '(') {
                        where = where.slice(0, -1);
                    } else {
                        where += ") " + (isor ? 'or' : 'and') + " ";
                    }
                } else {
                    // 有数组的话，就是or
                    if (mysearchobj[i] instanceof Array) {
                        where += " (";
                        mysearchobj[i].forEach(function (ceil) {
                            if (ceil.type == "in") {
                                where += " " + i + " in (?) or ";
                            } else {
                                where += " " + i + " " + ceil.type + " ? or ";
                            }
                            paramArr.push(ceil.type == "like" ? (ceil.value.indexOf("%") != -1 ? ceil.value : ("%" + ceil.value + "%")) : ceil.value);
                        });
                        where = where.replace(/and *$/, "").replace(/or *$/, "");
                        if (where[where.length - 1] == '(') {
                            where = where.slice(0, -1);
                        } else {
                            where += ") " + (isor ? 'or' : 'and') + " ";
                        }
                    } else {
                        if (mysearchobj[i].type == "in") {
                            where += " " + i + " in (?) " + (isor ? 'or' : 'and') + " ";
                        } else {
                            where += " " + i + " " + mysearchobj[i].type + " ? " + (isor ? 'or' : 'and') + " ";
                        }
                        paramArr.push(mysearchobj[i].type == "like" ? (mysearchobj[i].value.indexOf("%") != -1 ? mysearchobj[i].value : ("%" + mysearchobj[i].value + "%")) : mysearchobj[i].value);
                    }
                }
            }
            where && (where = where.replace(/and *$/, "").replace(/or *$/, ""));
        }
        
        (where.trim()) && (where = ' where' + where.replace(/and *$/, "").replace(/or *$/, ""));
        
        // var sql = "select " + (option && option.tablename || tablename) + ".* from " + (option && option.tablename || tablename) + where + (option.sortkey || sortkey ? " order by " + (option.sortkey || sortkey) + " DESC" : "")
        var sortk = option.sortkey || sortkey;
        var gby = option && option.groupby;
        var sql = "select " + (option.sum ? "count(*) as sum" : (option && option.selectstr ? option.selectstr : ((option && option.tablename || tablename) + ".*"))) + (option && option.beforefrom ? ' ' + option.beforefrom : '') + " from " + (option && option.vt ? option.vt : (option && option.tablename || tablename)) + (option && option.beforewhere ? ' ' + option.beforewhere : '') + where + (!gby && (sortk && sortk != 'NOSORT') ? " order by " + sortk + (option.nodesc ? '' : " DESC") : "") + (gby ? ' group by ' + option.groupby : '')
        
        // 查询列表和查询总数
        function _getList(callback) {
            const query = pool.query(sql + " limit ?, ?", paramArr.concat(pagingParam), function (err, data) {
                if (env.getEnv() == 'local') {
                    console.log('queryList=>', query.sql);
                }
                callback && callback(err, data);
            })
        }

        function _getCount (callback) {
            pool.query("select count(*) as count from (" + sql + ") counttable", paramArr, function(err, data) {
                // console.log(err, data);
                callback && callback(err, data && data[0] ? data[0].count : 0);
            })
        }

        // 总查询结果
        async.parallel({
            data: _getList,
            count: _getCount
        }, function(err, data) {
            callback(err, data);
        })
    }


    /**
     * 查询所有数据，返回特定的字段，不限长度
     * @Author   guanyanchang
     * @DateTime 2020-08-24
     * @param    {Function}  callback  回调函数
     * @param    {Array}    colList 字段数组 例如['name','age']
     */
    function queryAllList(callback, colList, opts) {
        var cols = '';
        if(colList && colList.length>0) {
            cols = colList.reduce(function (cols, current, index){
                if(index===0) {
                    return cols + current;
                } else {
                    return cols + ',' + current;
                }
            }, '')
        } else {
            cols = '*';
        }
        var sql = "select " + cols + " from "+ (opts && opts.tablename || tablename);
        // console.log('sql',sql);
        pool.query(sql, function(err, data) {
            // console.log('查询小喇叭所有数据',err, data);
            callback && callback(err, data);
        })
    }

    // 自定义sql
    function custom(callback, sql, values) {
        pool.query(sql, values || [], function (err, result) {
            callback(err, result);
        });
    }

    // 支持异步调用
    function useAsync(func) {
        return (...argus) => {
            return new Promise((resolve, reject) => {
                func((err, result) => {
                    if (err) {
                        reject(err);
                        return;
                    }
                    resolve(result)
                }, ...argus)
            })
        }
    }

    // 返回
    return {
        queryById,
        add,
        update,
        queryList,
        addone,
        custom,
        queryAllList: queryAllList,
        queryByIdAsync: useAsync(queryById),
        addAsync: useAsync(add),
        updateAsync: useAsync(update),
        queryListAsync: useAsync(queryList),
        addoneAsync: useAsync(addone),
        queryAllListAsync: useAsync(queryAllList),
        customAsync: useAsync(custom),
    }
}
